Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Example: Automatically Generate the Minimum Grouping Sets for a Cube

This example shows how to automatically generate a minimum set of materialized views for the cube `UNITS_CUBE` in the `GLOBAL` schema. This cube has dimensions for Channel, Customer, Product, and Time. The Customer dimension has two hierarchies, which share the same lowest level.

The dimensions of the `UNITS_CUBE` are described in Table 27-1. The levels in each hierarchy are listed from lowest (the "leaf" level) to highest (the most aggregate). The position of a level in a hierarchy determines whether it is among the minimum grouping sets. For the rules for creating the minimum grouping sets, refer to "MINIMUM Grouping Sets".

Table 27-1 Dimensions of GLOBAL.UNITS_CUBE

Dimension | Hierarchy | Levels
--- | --- | ---
`CHANNEL` | `CHANNEL_ROLLUP` | `Channel` `All_Channels`
`CUSTOMER` | `MARKET_ROLLUP` | `Ship_to` `Account` `Market_Segment` `Total_Market`
| `SHIPMENTS_ROLLUP` | `Ship_to` `Warehouse` `Region` `All_Customers`
`PRODUCT` | `PRODUCT_ROLLUP` | `Item` `Family` `Class` `Total_Product`
`TIME` | `CALENDAR` | `Month` `Quarter` `Year`

To generate the materialized views:

1. Identify a scripts directory. The directory can be specified in the `UTL_FILE_DIR` initialization parameter, or you can define a directory object with statements like the following.

       CREATE OR REPLACE DIRECTORY GLOBALDIR AS '/users/global/scripts';
       GRANT ALL ON DIRECTORY GLOBALDIR TO PUBLIC;

2. Generate the scripts for the dimension materialized views. The following statements create the scripts `chanmv.sql`, `custmv.sql`, `prodmv.sql`, and `timemv.sql` in the `/users/global/scripts` directory.

       exec dbms_odm.createdimmv_gs
             ('global', 'channel', 'chanmv.sql', 'GLOBALDIR');
       exec dbms_odm.createdimmv_gs
             ('global', 'customer', 'custmv.sql', 'GLOBALDIR');
       exec dbms_odm.createdimmv_gs
             ('global', 'product', 'prodmv.sql', 'GLOBALDIR');
       exec dbms_odm.createdimmv_gs
             ('global', 'time', 'timemv.sql', 'GLOBALDIR');

3. Run these scripts to create the dimension materialized views. The scripts will create one materialized view for the `CHANNEL` dimension, one for the `PRODUCT` dimension, one for the `TIME` dimension, and one for each of the two hierarchies of the `CUSTOMER` dimension.

       @/users/global/scripts/chanmv
       @/users/global/scripts/custmv
       @/users/global/scripts/prodmv
       @/users/global/scripts/timemv

4. Once you have created the dimension materialized views, execute the following procedure to create a script for the fact materialized view.

       exec dbms_odm.createstdfactmv
               ('global', 'units_cube', 'units_cube_mv.sql', 'GLOBALDIR',
                 false, 'MINIMUM');

   This statement creates a script called `units_cube_mv.sql` in the directory `/users/global/scripts`.

5. Run the script to create the fact materialized view.

       @/users/global/scripts/units_cube_mv

The script creates a materialized view with the grouping sets identified in Table 27-2.

Table 27-2 Minimum Grouping Sets for Units Cube

CHANNEL_DIM | CUSTOMER_DIM | PRODUCT_DIM | TIME_DIM
--- | --- | --- | ---
`CHANNEL` | `SHIP_TO` (`MARKET_ROLLUP` hierarchy) | `ITEM` | `QUARTER`
`CHANNEL` | `SHIP_TO` (`MARKET_ROLLUP` hierarchy) | `ITEM` | `YEAR`
`CHANNEL` | `SHIP_TO` (`MARKET_ROLLUP` hierarchy) | `FAMILY` | `MONTH`
`CHANNEL` | `SHIP_TO` (`MARKET_ROLLUP` hierarchy) | `TOTAL_PRODUCT` | `MONTH`
`CHANNEL` | `SHIP_TO` (`SHIPMENTS_ROLLUP` hierarchy) | `ITEM` | `QUARTER`
`CHANNEL` | `SHIP_TO` (`SHIPMENTS_ROLLUP` hierarchy) | `ITEM` | `YEAR`
`CHANNEL` | `SHIP_TO` (`SHIPMENTS_ROLLUP` hierarchy) | `FAMILY` | `MONTH`
`CHANNEL` | `SHIP_TO` (`SHIPMENTS_ROLLUP` hierarchy) | `TOTAL_PRODUCT` | `MONTH`
`CHANNEL` | `ACCOUNT` | `ITEM` | `MONTH`
`CHANNEL` | `TOTAL_MARKET` | `ITEM` | `MONTH`
`CHANNEL` | `ALL_CUSTOMERS` | `ITEM` | `MONTH`
`ALL_CHANNELS` | `SHIP_TO` (`MARKET_ROLLUP` hierarchy) | `ITEM` | `MONTH`
`ALL_CHANNELS` | `SHIP_TO` (`SHIPMENTS_ROLLUP` hierarchy) | `ITEM` | `MONTH`
`ALL_CHANNELS` | `ACCOUNT` | `FAMILY` | `QUARTER`
`ALL_CHANNELS` | `WAREHOUSE` | `FAMILY` | `QUARTER`
`ALL_CHANNELS` | `TOTAL_MARKET` | `TOTAL_PRODUCT` | `YEAR`
`ALL_CHANNELS` | `ALL_CUSTOMERS` | `TOTAL_PRODUCT` | `YEAR`